package ch.fhnw.qualiscan.export;

import jxl.Workbook;
import jxl.format.BoldStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;

import java.io.File; 
import java.io.IOException;
import java.io.InputStream;
import java.io.PipedInputStream;
import java.io.PipedOutputStream;
import java.util.ArrayList;
import java.util.Date; 
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import ch.fhnw.qualiscan.application.models.ApiModel;
import ch.fhnw.qualiscan.application.models.DomainModel;
import ch.fhnw.qualiscan.application.models.QuestionModel;
import ch.fhnw.qualiscan.application.models.RoleModel;
import ch.fhnw.qualiscan.application.models.UserModel;
import ch.fhnw.qualiscan.core.db.pojos.Answer;
import ch.fhnw.qualiscan.core.db.pojos.Domain;
import ch.fhnw.qualiscan.core.db.pojos.Question;
import ch.fhnw.qualiscan.core.db.pojos.Role;
import ch.fhnw.qualiscan.core.db.pojos.SurveyUserRole;

import com.vaadin.terminal.StreamResource.StreamSource;
import com.vaadin.ui.Window;


public class Excel implements StreamSource {
	
	private SurveyUserRole sur;
	
	private RoleModel roleModel;
	private UserModel userModel;
	private DomainModel domainModel;
	private ApiModel apiModel;
	private QuestionModel questionModel;
	
	public Excel(SurveyUserRole sur) {
		this.sur = sur;
		roleModel = new RoleModel();
		userModel = new UserModel();
		domainModel = new DomainModel();
		apiModel = new ApiModel();
		questionModel = new QuestionModel();
		
	}
	
	public InputStream getStream() {

		try {
			PipedInputStream in = new PipedInputStream();
			final PipedOutputStream out = new PipedOutputStream(in);

			new Thread(new Runnable() {
				public void run() {
					try {
						GenerateExcel(out);
					} catch (Throwable e) {
						throw new RuntimeException(e);
					}
				}
			}).start();
			return in;
		} catch(Throwable e) {
			
		}
		
		return null;
		
		
	}
	
	public void GenerateExcel(PipedOutputStream out) throws RowsExceededException, WriteException, IOException {
		
		
		List<Role> roleList;
		roleList = roleModel.getRoles(sur.getSurvey());
		
		//Styles
		WritableFont h1font = new WritableFont(WritableFont.ARIAL, 14);
		h1font.setBoldStyle(WritableFont.BOLD);
		WritableCellFormat h1 = new WritableCellFormat (h1font); 
		
		WritableFont h2font = new WritableFont(WritableFont.ARIAL, 12);
		h2font.setBoldStyle(WritableFont.BOLD);
		WritableCellFormat h2 = new WritableCellFormat(h2font);
		
		WritableFont boldfont = new WritableFont(WritableFont.ARIAL, 9);
		boldfont.setBoldStyle(WritableFont.BOLD);
		WritableCellFormat bold = new WritableCellFormat(boldfont);
		
		WritableWorkbook workbook = null;
		
		//Create Workbook
		workbook = Workbook.createWorkbook(out);
		
		/*
		 * Data 
		 */
		
		//Domains
		Set<Domain> domains = sur.getSurvey().getDomains();
		Iterator<Domain> domainIterator = domains.iterator();
		/*
		 * Create Sheets
		 */
		//All roles
		WritableSheet allRolesSheet = workbook.createSheet("All Domains", 0);
		
		//Roles in the Survey
		List<WritableSheet> sheetList = new ArrayList<WritableSheet>();
		
		int domainCounter = 1;
		while(domainIterator.hasNext()) {
			Domain domain = domainIterator.next();
			
			WritableSheet sheet = workbook.createSheet(domainModel.getDomainText(domain, sur.getUser().getLanguage()).getText(), domainCounter);
			sheetList.add(sheet);
			
			domainCounter++;
		}
		
		/*
		 * Data in All Roles
		 */
		
		//Title
		Label titleAllRoles = new Label(0, 1, sur.getSurvey().getTitle(), h1); 
		allRolesSheet.addCell(titleAllRoles);
		
		domainIterator = domains.iterator();
		
		//Data
		int startRow = 3;
		int startColumn = 0;
		int column = startColumn;
		//For Labels
		int row = startRow;
		//All Roles counter
		int row2 = startRow+3;
		//Roles counter
		int row3 = startRow+4;
		
		while(domainIterator.hasNext()) {
			Domain domain = domainIterator.next();

			Label title = new Label(column, row, domainModel.getDomainText(domain, sur.getUser().getLanguage()).getText().toString(), h2);
			allRolesSheet.addCell(title);
			
			row += 2;
			
			Label disagree = new Label(column+1, row, "Disagree", bold);
			allRolesSheet.addCell(disagree);
			
			Label partDisagree = new Label(column+2, row, "Partially Disagree", bold);
			allRolesSheet.addCell(partDisagree);
			 
			Label partAgree = new Label(column+3, row, "Partially Agree", bold);
			allRolesSheet.addCell(partAgree);
			
			Label agree = new Label(column+4, row, "Agree", bold);
			allRolesSheet.addCell(agree);
			
			Label notApplicable = new Label(column+5, row, "Not Applicable", bold);
			allRolesSheet.addCell(notApplicable);
			
			Label sumLabel = new Label(column+6, row, "Sum", bold);
			allRolesSheet.addCell(sumLabel);
			
			Label allRoles = new Label(column, ++row, "All Roles", bold); 
			allRolesSheet.addCell(allRoles);
			
			for(int r = 0; r < roleList.size(); r++) {
				Label role = new Label(column, ++row, userModel.getRoleText(roleList.get(r), sur.getUser().getLanguage()).getTitle(), bold);
				allRolesSheet.addCell(role);
			}
			
			/*
			 * BEGIN FIRST SHEEET
			 */
			int nDisagree = 0;
			int nPartDisagree = 0;
			int nPartAgree = 0;
			int nAgree = 0;
			int nNotApplicable = 0;
			int sum = 0;
			List<Answer> answerList = apiModel.getAnswersForDomain(sur.getSurvey(), domain);
				
			for (int a = 0; a < answerList.size(); a++) {
				if(answerList.get(a).getAnswer() == 1) {
					nDisagree++;
				} else if(answerList.get(a).getAnswer() == 2) {
					nPartDisagree++;
				} else if(answerList.get(a).getAnswer() == 3) {
					nPartAgree++;
				} else if(answerList.get(a).getAnswer() == 4) {
					nAgree++;
				} else if(answerList.get(a).getAnswer() == 5) {
					nNotApplicable++;
				}
					
				sum++;
			}
				
			WritableCell nDisagreeAll = new Number(column+1, row2, nDisagree);
			allRolesSheet.addCell(nDisagreeAll);
			
			WritableCell nPartDisagreeAll = new Number(column+2, row2, nPartDisagree);
			allRolesSheet.addCell(nPartDisagreeAll);
			
			WritableCell nPartAgreeAll = new Number(column+3, row2, nPartAgree);
			allRolesSheet.addCell(nPartAgreeAll);
				
			WritableCell nAgreeAll = new Number(column+4, row2, nAgree);
			allRolesSheet.addCell(nAgreeAll);
				
			WritableCell nNotApplicableAll = new Number(column+5, row2, nNotApplicable);
			allRolesSheet.addCell(nNotApplicableAll);
				
			WritableCell sumAll = new Number(column+6, row2, sum);
			allRolesSheet.addCell(sumAll);
			
			for(int r = 0; r < roleList.size(); r++) {
				nDisagree = 0;
				nPartDisagree = 0;
				nPartAgree = 0;
				nAgree = 0;
				nNotApplicable = 0;
				sum = 0;
				
				answerList = apiModel.getAnswersForDomain(sur.getSurvey(), domain, roleList.get(r));
				
				for (int a = 0; a < answerList.size(); a++) {
					if(answerList.get(a).getAnswer() == 1) {
						nDisagree++;
					} else if(answerList.get(a).getAnswer() == 2) {
						nPartDisagree++;
					} else if(answerList.get(a).getAnswer() == 3) {
						nPartAgree++;
					} else if(answerList.get(a).getAnswer() == 4) {
						nAgree++;
					} else if(answerList.get(a).getAnswer() == 5) {
						nNotApplicable++;
					}
						
					sum++;
				}
					
				nDisagreeAll = new Number(column+1, row3, nDisagree);
				allRolesSheet.addCell(nDisagreeAll);
				
				nPartDisagreeAll = new Number(column+2, row3, nPartDisagree);
				allRolesSheet.addCell(nPartDisagreeAll);
				
				nPartAgreeAll = new Number(column+3, row3, nPartAgree);
				allRolesSheet.addCell(nPartAgreeAll);
					
				nAgreeAll = new Number(column+4, row3, nAgree);
				allRolesSheet.addCell(nAgreeAll);
					
				nNotApplicableAll = new Number(column+5, row3, nNotApplicable);
				allRolesSheet.addCell(nNotApplicableAll);
					
				sumAll = new Number(column+6, row3, sum);
				allRolesSheet.addCell(sumAll);
				
				row3++;
			}
			
			//Row counters
			row3 = row3 + 6;
			row2 = row2 + roleList.size()+6;
			row += 3;
			
		}
		/*
		 * END FIRST SHEET
		 */
		
		//Each Domain with questions
		domainIterator = domains.iterator();
		int counter = 0;
		startColumn = 0;
		column = startColumn+1;
		
		
		while (domainIterator.hasNext()) {
			
			int titleRow = 1;
			row = startRow;
			row3 = startRow+4;
			row2 = startRow+3;
			
			Domain domain = domainIterator.next();
			WritableSheet currentSheet = sheetList.get(counter);
			
			Label title = new Label(startColumn, titleRow, domainModel.getDomainText(domain, sur.getUser().getLanguage()).getText(), h1);
			currentSheet.addCell(title);
			
			Set<Question> questions = domain.getQuestions();
			Iterator<Question> questionIterator = questions.iterator();
			
			int numbering = 1;
			while(questionIterator.hasNext()) {
				Question question = questionIterator.next();
				Set<Question> subQuestionSet = question.getSubquestions();
				
				Label questionLabel = new Label(startColumn, row, ""+numbering+". "+questionModel.getQuestionText(sur.getUser().getLanguage(), question).getText(), bold);
				currentSheet.addCell(questionLabel);
				
				row += 2;
				
				if (subQuestionSet.size() > 0) {
				/*
				 * SUBQUESTION SUBQUESTION SUBQUESTION SUBQUESTION SUBQUESTION SUBQUESTION SUBQUESTION SUBQUESTION SUBQUESTION SUBQUESTION
				 */
					
					Iterator<Question> subquestionIterator = subQuestionSet.iterator();
					
					int subNumbering = 1;
					
					while(subquestionIterator.hasNext()) {
						Question subquestion = subquestionIterator.next();
						Label subTitle = new Label(startColumn+1, ++row, ""+numbering+"."+subNumbering+". "+questionModel.getQuestionText(sur.getUser().getLanguage(), subquestion).getText(), bold);
						currentSheet.addCell(subTitle);
						
						row += 2;
						
						Label disagree = new Label(column+1, row, "Disagree", bold);
						currentSheet.addCell(disagree);
						
						Label partDisagree = new Label(column+2, row, "Partially Disagree", bold);
						currentSheet.addCell(partDisagree);
						 
						Label partAgree = new Label(column+3, row, "Partially Agree", bold);
						currentSheet.addCell(partAgree);
						
						Label agree = new Label(column+4, row, "Agree", bold);
						currentSheet.addCell(agree);
						
						Label notApplicable = new Label(column+5, row, "Not Applicable", bold);
						currentSheet.addCell(notApplicable);
						
						Label sumLabel = new Label(column+6, row, "Sum", bold);
						currentSheet.addCell(sumLabel);
						
						Label allRoles = new Label(column, ++row, "All Roles", bold); 
						currentSheet.addCell(allRoles);
						
						row2 = row;
						
						for(int r = 0; r < roleList.size(); r++) {
							Label role = new Label(column, ++row, userModel.getRoleText(roleList.get(r), sur.getUser().getLanguage()).getTitle(), bold);
							currentSheet.addCell(role);
						}
						
						int nDisagree = 0;
						int nPartDisagree = 0;
						int nPartAgree = 0;
						int nAgree = 0;
						int nNotApplicable = 0;
						int sum = 0;
						
						List<Answer> answerList = apiModel.getAnswersForQuestion(sur.getSurvey(), subquestion);
						
						for (int a = 0; a < answerList.size(); a++) {
							if(answerList.get(a).getAnswer() == 1) {
								nDisagree++;
							} else if(answerList.get(a).getAnswer() == 2) {
								nPartDisagree++;
							} else if(answerList.get(a).getAnswer() == 3) {
								nPartAgree++;
							} else if(answerList.get(a).getAnswer() == 4) {
								nAgree++;
							} else if(answerList.get(a).getAnswer() == 5) {
								nNotApplicable++;
							}
								
							sum++;
						}
							
						WritableCell nDisagreeAll = new Number(column+1, row2, nDisagree);
						currentSheet.addCell(nDisagreeAll);
						
						WritableCell nPartDisagreeAll = new Number(column+2, row2, nPartDisagree);
						currentSheet.addCell(nPartDisagreeAll);
						
						WritableCell nPartAgreeAll = new Number(column+3, row2, nPartAgree);
						currentSheet.addCell(nPartAgreeAll);
							
						WritableCell nAgreeAll = new Number(column+4, row2, nAgree);
						currentSheet.addCell(nAgreeAll);
							
						WritableCell nNotApplicableAll = new Number(column+5, row2, nNotApplicable);
						currentSheet.addCell(nNotApplicableAll);
							
						WritableCell sumAll = new Number(column+6, row2, sum);
						currentSheet.addCell(sumAll);
						
						row3 = row2+1;
						for(int r = 0; r < roleList.size(); r++) {
							nDisagree = 0;
							nPartDisagree = 0;
							nPartAgree = 0;
							nAgree = 0;
							nNotApplicable = 0;
							sum = 0;
							
							answerList = apiModel.getAnswersForQuestion(sur.getSurvey(), subquestion, roleList.get(r));
							
							for (int a = 0; a < answerList.size(); a++) {
								if(answerList.get(a).getAnswer() == 1) {
									nDisagree++;
								} else if(answerList.get(a).getAnswer() == 2) {
									nPartDisagree++;
								} else if(answerList.get(a).getAnswer() == 3) {
									nPartAgree++;
								} else if(answerList.get(a).getAnswer() == 4) {
									nAgree++;
								} else if(answerList.get(a).getAnswer() == 5) {
									nNotApplicable++;
								}
									
								sum++;
							}
								
							nDisagreeAll = new Number(column+1, row3, nDisagree);
							currentSheet.addCell(nDisagreeAll);
							
							nPartDisagreeAll = new Number(column+2, row3, nPartDisagree);
							currentSheet.addCell(nPartDisagreeAll);
							
							nPartAgreeAll = new Number(column+3, row3, nPartAgree);
							currentSheet.addCell(nPartAgreeAll);
								
							nAgreeAll = new Number(column+4, row3, nAgree);
							currentSheet.addCell(nAgreeAll);
								
							nNotApplicableAll = new Number(column+5, row3, nNotApplicable);
							currentSheet.addCell(nNotApplicableAll);
								
							sumAll = new Number(column+6, row3, sum);
							currentSheet.addCell(sumAll);
							
							row3++;
						}
						
						
						row += 2;
						subNumbering++;
					}
					row -= 2;
				}
				else {
				/*
				 * NO SUBQUESTION NO SUBQUESTION NO SUBQUESTION NO SUBQUESTION NO SUBQUESTION
				 */
					
					Label disagree = new Label(column+1, row, "Disagree", bold);
					currentSheet.addCell(disagree);
					
					Label partDisagree = new Label(column+2, row, "Partially Disagree", bold);
					currentSheet.addCell(partDisagree);
					 
					Label partAgree = new Label(column+3, row, "Partially Agree", bold);
					currentSheet.addCell(partAgree);
					
					Label agree = new Label(column+4, row, "Agree", bold);
					currentSheet.addCell(agree);
					
					Label notApplicable = new Label(column+5, row, "Not Applicable", bold);
					currentSheet.addCell(notApplicable);
					
					Label sumLabel = new Label(column+6, row, "Sum", bold);
					currentSheet.addCell(sumLabel);
					
					Label allRoles = new Label(column, ++row, "All Roles", bold); 
					currentSheet.addCell(allRoles);
					
					row2 = row;
					
					for(int r = 0; r < roleList.size(); r++) {
						Label role = new Label(column, ++row, userModel.getRoleText(roleList.get(r), sur.getUser().getLanguage()).getTitle(), bold);
						currentSheet.addCell(role);
					}
					
					int nDisagree = 0;
					int nPartDisagree = 0;
					int nPartAgree = 0;
					int nAgree = 0;
					int nNotApplicable = 0;
					int sum = 0;
					
					List<Answer> answerList = apiModel.getAnswersForQuestion(sur.getSurvey(), question);
					
					for (int a = 0; a < answerList.size(); a++) {
						if(answerList.get(a).getAnswer() == 1) {
							nDisagree++;
						} else if(answerList.get(a).getAnswer() == 2) {
							nPartDisagree++;
						} else if(answerList.get(a).getAnswer() == 3) {
							nPartAgree++;
						} else if(answerList.get(a).getAnswer() == 4) {
							nAgree++;
						} else if(answerList.get(a).getAnswer() == 5) {
							nNotApplicable++;
						}
							
						sum++;
					}
						
					WritableCell nDisagreeAll = new Number(column+1, row2, nDisagree);
					currentSheet.addCell(nDisagreeAll);
					
					WritableCell nPartDisagreeAll = new Number(column+2, row2, nPartDisagree);
					currentSheet.addCell(nPartDisagreeAll);
					
					WritableCell nPartAgreeAll = new Number(column+3, row2, nPartAgree);
					currentSheet.addCell(nPartAgreeAll);
						
					WritableCell nAgreeAll = new Number(column+4, row2, nAgree);
					currentSheet.addCell(nAgreeAll);
						
					WritableCell nNotApplicableAll = new Number(column+5, row2, nNotApplicable);
					currentSheet.addCell(nNotApplicableAll);
						
					WritableCell sumAll = new Number(column+6, row2, sum);
					currentSheet.addCell(sumAll);
					
					row3 = row2+1;
					for(int r = 0; r < roleList.size(); r++) {
						nDisagree = 0;
						nPartDisagree = 0;
						nPartAgree = 0;
						nAgree = 0;
						nNotApplicable = 0;
						sum = 0;
						
						answerList = apiModel.getAnswersForQuestion(sur.getSurvey(), question, roleList.get(r));
						
						for (int a = 0; a < answerList.size(); a++) {
							if(answerList.get(a).getAnswer() == 1) {
								nDisagree++;
							} else if(answerList.get(a).getAnswer() == 2) {
								nPartDisagree++;
							} else if(answerList.get(a).getAnswer() == 3) {
								nPartAgree++;
							} else if(answerList.get(a).getAnswer() == 4) {
								nAgree++;
							} else if(answerList.get(a).getAnswer() == 5) {
								nNotApplicable++;
							}
								
							sum++;
						}
							
						nDisagreeAll = new Number(column+1, row3, nDisagree);
						currentSheet.addCell(nDisagreeAll);
						
						nPartDisagreeAll = new Number(column+2, row3, nPartDisagree);
						currentSheet.addCell(nPartDisagreeAll);
						
						nPartAgreeAll = new Number(column+3, row3, nPartAgree);
						currentSheet.addCell(nPartAgreeAll);
							
						nAgreeAll = new Number(column+4, row3, nAgree);
						currentSheet.addCell(nAgreeAll);
							
						nNotApplicableAll = new Number(column+5, row3, nNotApplicable);
						currentSheet.addCell(nNotApplicableAll);
							
						sumAll = new Number(column+6, row3, sum);
						currentSheet.addCell(sumAll);
						
						row3++;
					}
					
				}
				
				row += 3;
				
				numbering++;
			}
			 
			 
			 counter++;
		}

		workbook.write();
		workbook.close();
		
		out.close();
		

	}

	
}

